<?php

class Model_Transaction
{

    //Statuses

    const PENDING  = 1;
    const BORROW   = 2;
    const GIVEBACK = 3;

    public static function getByCardId($params)
    {
        try
        {
            $db = Core_Global::getDatabase();

            $stmt = $db->prepare("SELECT Transaction.*, Book.Title As BookTitle FROM Transaction
                                  INNER JOIN Book USING(BookID)
                                  WHERE Transaction.CardID = :p_CardID 
                                    AND Transaction.Status = :p_Status 
                                  ORDER BY Type ASC, CreateDate DESC");

            $stmt->bindValue(':p_CardID', $params['CardID'], PDO::PARAM_INT);
            $stmt->bindValue(':p_Status', $params['Status'], PDO::PARAM_INT);

            $stmt->execute();

            return $stmt->fetchAll();
        }
        catch (Zend_Db_Exception $e)
        {
            throw new Zend_Db_Exception($e);
        }
    }

    public static function find($params, &$count)
    {
        try
        {
            $db = Core_Global::getDatabase();

            $params['Offset'] = ($params['Page'] - 1) * $params['Limit'];

            $sql = "SELECT SQL_CALC_FOUND_ROWS Transaction.*, Account.Name AS AccountName, Book.Title AS BookTitle FROM Transaction
                    INNER JOIN Card USING (CardID)
                    INNER JOIN Account ON (Account.CardID = Card.CardID) 
                    INNER JOIN Book ON (Transaction.BookID = Book.BookID) WHERE 1=1";

            if ($params['FromDate'] && $params['ToDate'])
            {
                $sql .= " AND Transaction.CreateDate BETWEEN :p_FromDate AND :p_ToDate";
            }

            if ($params['Status'])
            {
                $sql .= " AND Transaction.Status = :p_Status";
            }

            if ($params['q'])
            {
                $sql .= " AND Account.Name LIKE '%" . $params['q'] . "%'";
            }

            if ($params['Type'])
            {
                $sql .= " AND Transaction.Type = :p_Type";
            }

            $sql .= "  ORDER BY Transaction.Type ASC, Transaction.CreateDate DESC LIMIT :p_Offset, :p_Limit";

            $stmt = $db->prepare($sql);

            $stmt->bindValue(':p_Limit', $params['Limit'], PDO::PARAM_INT);
            $stmt->bindValue(':p_Offset', $params['Offset'], PDO::PARAM_INT);

            if ($params['FromDate'] && $params['ToDate'])
            {
                $stmt->bindValue(':p_FromDate', $params['FromDate'], PDO::PARAM_INT);
                $stmt->bindValue(':p_ToDate', $params['ToDate'], PDO::PARAM_INT);
            }

            if ($params['Type'])
            {
                $stmt->bindValue(':p_Type', $params['Type'], PDO::PARAM_INT);
            }

            if ($params['Status'])
            {
                $stmt->bindValue(':p_Status', $params['Status'], PDO::PARAM_INT);
            }

            $stmt->execute();

            $data = $stmt->fetchAll();

            $stmt = $db->query('SELECT FOUND_ROWS()');

            $count = $stmt->fetchColumn();

            return $data;
        }
        catch (Zend_Db_Exception $e)
        {
            throw new Zend_Db_Exception($e);
        }
    }

    public static function create($params)
    {
        try
        {
            $db = Core_Global::getDatabase();

            $stmt = $db->prepare("INSERT INTO Transaction VALUES(:p_TransactionID, :p_CardID, :p_BookID, :p_Type, :p_CreateDate, :p_Status, :p_Comment)");

            $stmt->bindValue(':p_TransactionID', null, PDO::PARAM_NULL);
            $stmt->bindValue(':p_CardID', $params['CardID'], PDO::PARAM_INT);
            $stmt->bindValue(':p_BookID', $params['BookID'], PDO::PARAM_INT);
            $stmt->bindValue(':p_Type', $params['Type'], PDO::PARAM_INT);
            $stmt->bindValue(':p_CreateDate', intval(microtime(true)), PDO::PARAM_INT);
            $stmt->bindValue(':p_Status', $params['Status'], PDO::PARAM_INT);
            $stmt->bindValue(':p_Comment', $params['Comment'], PDO::PARAM_STR);

            $stmt->execute();

            $transactionId = $db->lastInsertId();

            return $transactionId;
        }
        catch (Zend_Db_Exception $e)
        {
            throw new Zend_Db_Exception($e);
        }
    }

    public static function get($params)
    {
        try
        {
            $db = Core_Global::getDatabase();

            $sql = "SELECT * FROM Transaction WHERE TransactionID = :p_TransactionID";

            $stmt = $db->prepare($sql);

            $stmt->bindValue(':p_TransactionID', $params['TransactionID'], PDO::PARAM_INT);

            $stmt->execute();

            return $stmt->fetch();
        }
        catch (Zend_Db_Exception $e)
        {
            throw new Zend_Db_Exception($e);
        }
    }

    public static function edit($params)
    {
        try
        {
            $db = Core_Global::getDatabase();

            //Get current Transaction
            $transaction = self::get(array('TransactionID' => $params['TransactionID']));

            if (!empty($transaction))
            {
                //"Delete" old transaction                                           
                $stmt = $db->prepare("UPDATE Transaction SET Status = 0 WHERE TransactionID = :p_TransactionID");

                $stmt->bindValue(':p_TransactionID', $params['TransactionID'], PDO::PARAM_INT);

                $stmt->execute();

                //Create new transaction
                $transactionId = self::create(array(
                            'CardID'     => $transaction['CardID'],
                            'BookID'     => $transaction['BookID'],
                            'Type'       => $params['Type'],
                            'CreateDate' => intval(microtime(true)),
                            'Status'     => 1,
                            'Comment'    => $transaction['Comment'],
                        ));

                return $transactionId;
            }

            return 0;
        }
        catch (Zend_Db_Exception $e)
        {
            throw new Zend_Db_Exception($e);
        }
    }

    public static function delete($params)
    {
        try
        {
            $db = Core_Global::getDatabase();

            $stmt = $db->prepare("DELETE FROM Transaction WHERE TransactionID = :p_TransactionID");

            $stmt->bindValue(':p_TransactionID', $params['TransactionID'], PDO::PARAM_INT);

            return $stmt->execute();
        }
        catch (Zend_Db_Exception $e)
        {
            throw new Zend_Db_Exception($e);
        }
    }

    public static function typeToString($status)
    {
        switch ($status)
        {
            case 1:
                return "Đang đặt mượn";
                break;
            case 2:
                return "Đang mượn";
                break;
            case 3:
                return "Đã trả";
                break;
            default:
                break;
        }
    }

}
